﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Project.dalSql;
using System.Data.SqlClient;
using WISD.Common;
using WISD.CarFriend.Model.CFModel;
using WISD.DBUtility.DBOperate;

namespace WISD.CarFriend.BLL.OA
{
    public class ConsumeModule
    {
        public static SqlObject sqlobj = null;
        public static DataTable dt = null;
        private static List<SqlObject> SqlObj = null;

        /// <summary>
        /// 选择会员信息
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static string GetCardDetail(int PageSize, int PageIndex, string condition)
        {
            StringBuilder tblName = new StringBuilder(); //从那些表里面进行查询
            int RsCount = 0;
            tblName.Append(" T_CF_OA_MemberCard a,T_CF_OA_Member b");

            string strOrder = " a.MemberID desc ";  //排序的字段
            StringBuilder strGetFields = new StringBuilder(); //返回的列datediff(d,updatedate,getdate())
            strGetFields.Append("a.MemberCardID main,a.MemberCardID,a.MemberID ,b.Name  ")
               .Append(" ,case a.State when 1 then '正常' when 2 then '挂失' when 3 then '冻结'  when 4 then '未审核'  else '' end State ,a.ResidualAmount ");

            StringBuilder strWhere = new StringBuilder(); //查询的条件
            strWhere.Append(" a.MemberID=b.MemberID ");

            //如果查询条件不为空的话
            if (!condition.Equals(""))
            {
                /*var Arr = condition.Split('@'); //分割成数组
                var type = Arr[0].ToString();
                var enterprise = Arr[1].ToString();
                if (!type.Equals("0"))
                {
                    strWhere.AppendFormat("  and a.ClientPro like N\'%{0}%\'  ", type);
                }
                if (!enterprise.Equals("0"))
                {

                    strWhere.AppendFormat("  and a.C_name like N\'%{0}%\' ", enterprise);
                }*/
            }
            object[] obj = new object[] { tblName.ToString(), strGetFields.ToString(), strOrder, PageSize, PageIndex, strWhere };
            DataTable dt = new DataTable();
            dt = dalSqlHelper.ExecuteProcedure(obj, ref RsCount);
            if (dt.Rows.Count > 0)
            {
                return dalSqlHelper.DtToSON(dt) + "@" + RsCount.ToString();
            }
            else
            {
                return "";
            }

        }


        /// <summary>
        /// 取得会员卡状态,会员卡编号,会员姓名,剩余金额
        /// </summary>
        /// <returns></returns>
        public static string GetCardInfo(string MemberCardID)
        {
            sqlobj = new SqlObject();
            StringBuilder sb = new StringBuilder();
            sb.Append(" select case a.State when 1 then '正常' when 2 then '挂失' when 3 then '冻结' when 4 then '未审核' else '' end State ")
              .Append(" ,a.MemberID,b.Name,a.ResidualAmount ")
              .Append(" from T_CF_OA_MemberCard a,T_CF_OA_Member b ")
              .Append(" where a.MemberID=b.MemberID and a.MemberCardID=@MemberCardID ");

            sqlobj.Sql = sb.ToString();
            sqlobj.PramS = new SqlParameter[] { 
               new SqlParameter("@MemberCardID",MemberCardID)
            };
            dt = dalSqlHelper.ExecuteSelect(sqlobj);
            return dalSqlHelper.DtToSON(dt);
        }


        /// <summary>
        ///  初始化页面和查询数据
        /// </summary>
        /// <param name="PageSize">每页显示的条数</param>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="condition">搜索条件</param>
        /// <returns></returns>
        public static string GetData(int PageSize, int PageIndex, string condition)
        {
            StringBuilder tblName = new StringBuilder(); //从那些表里面进行查询
            int RsCount = 0;
            tblName.Append(" T_CF_OA_Consume a,T_CF_OA_MemberCard b,T_CF_OA_Member c ,T_CF_OA_MemberConsumeItem d");

            string strOrder = " a.ConsumeID desc ";  //排序的字段
            StringBuilder strGetFields = new StringBuilder(); //返回的列datediff(d,updatedate,getdate())
            strGetFields.Append("a.ConsumeID main,a.MemberCardID ,b.MemberID,c.VehicleNo,a.consumeTime ")
                        .Append(",a.ConsumeLocation,d.ConsumeItem as ConsumeIDItem,a.Memo ");

            StringBuilder strWhere = new StringBuilder(); //查询的条件
            strWhere.Append(" a.IsDel='0' and a.MemberCardID=b.MemberCardID and b.MemberID=c.MemberID and a.ConsumeIDItem = d.Id ");

            //如果查询条件不为空的话
            if (!condition.Equals(""))
            {
                var Arr = condition.Split('@');
                var MemberCardID = Arr[0]; //会员卡编号
                var MemberID = Arr[1]; //会员编号
                var VipName = Arr[2];  //会员姓名
                var ConsumeTime = Arr[3];  //消费开始时间
                var ConsumeLocation = Arr[4];  //消费地点
                var ConsumeIDItem = Arr[5];  //消费项目
                var ConsumeEndTime = Arr[6];  //消费结束时间

                if (!MemberCardID.Equals("0"))
                {
                    strWhere.AppendFormat("  and a.MemberCardID=N\'{0}\'  ", MemberCardID);
                }
                if (!MemberID.ToString().Equals("0"))
                {
                    strWhere.AppendFormat("  and b.MemberID like N\'%{0}%\' ", MemberID);
                }
                if (!VipName.Equals("0"))
                {
                    strWhere.AppendFormat("  and c.Name like N\'%{0}%\' ", VipName);
                }
                if (!ConsumeTime.Equals("0"))
                {
                    strWhere.AppendFormat("  and a.ConsumeTime >= N\'{0}\'", ConsumeTime);
                }
                if (!ConsumeEndTime.Equals("0"))
                {
                    strWhere.AppendFormat("  and a.ConsumeTime <= N\'{0}\'", ConsumeEndTime);
                }
                if (!ConsumeLocation.Equals("0"))
                {
                    strWhere.AppendFormat("  and a.ConsumeLocation like N\'%{0}%\'", ConsumeLocation);
                }
                if (!ConsumeIDItem.Equals("0"))
                {
                    strWhere.AppendFormat("  and d.ConsumeItem like N\'%{0}%\'", ConsumeIDItem);
                }
            }
            object[] obj = new object[] { tblName.ToString(), strGetFields.ToString(), strOrder, PageSize, PageIndex, strWhere };
            DataTable dt = new DataTable();
            dt = dalSqlHelper.ExecuteProcedure(obj, ref RsCount);
            if (dt.Rows.Count > 0)
            {
                return dalSqlHelper.DtToSON(dt) + "@" + RsCount.ToString();
            }
            else
            {
                return "";
            }
        }


        /// <summary>
        ///  初始化页面和查询数据
        /// </summary>
        /// <param name="PageSize">每页显示的条数</param>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="condition">搜索条件</param>
        /// <returns></returns>
        public static string GetQueryData(int PageSize, int PageIndex, string condition)
        {
            StringBuilder tblName = new StringBuilder(); //从那些表里面进行查询
            int RsCount = 0;
            tblName.Append(" T_CF_OA_Consume a,T_CF_OA_MemberCard b,T_CF_OA_Member c ,T_CF_OA_MemberConsumeItem d");

            string strOrder = " a.ConsumeID desc ";  //排序的字段
            StringBuilder strGetFields = new StringBuilder(); //返回的列datediff(d,updatedate,getdate())
            strGetFields.Append("a.ConsumeID main,a.MemberCardID ,b.MemberID,c.VehicleNo,a.consumeTime ")
                        .Append(",a.ConsumeLocation,d.ConsumeItem as ConsumeIDItem,a.Memo,case when a.Memo1>0 then '是' else '否' end as State ");

            StringBuilder strWhere = new StringBuilder(); //查询的条件
            strWhere.Append(" a.IsDel='0' and a.MemberCardID=b.MemberCardID and b.MemberID=c.MemberID and a.ConsumeIDItem = d.Id ");

            //如果查询条件不为空的话
            if (!condition.Equals(""))
            {
                var Arr = condition.Split('@');
                var MemberCardID = Arr[0]; //会员卡编号
                var MemberID = Arr[1]; //会员编号
                var VipName = Arr[2];  //会员姓名
                var ConsumeTime = Arr[3];  //消费开始时间
                var ConsumeLocation = Arr[4];  //消费地点
                var ConsumeIDItem = Arr[5];  //消费项目
                var ConsumeEndTime = Arr[6];  //消费结束时间

                if (!MemberCardID.Equals("0"))
                {
                    strWhere.AppendFormat("  and a.MemberCardID=N\'{0}\'  ", MemberCardID);
                }
                if (!MemberID.ToString().Equals("0"))
                {
                    strWhere.AppendFormat("  and b.MemberID like N\'%{0}%\' ", MemberID);
                }
                if (!VipName.Equals("0"))
                {
                    strWhere.AppendFormat("  and c.Name like N\'%{0}%\' ", VipName);
                }
                if (!ConsumeTime.Equals("0"))
                {
                    strWhere.AppendFormat("  and a.ConsumeTime >= N\'{0}\'", ConsumeTime);
                }
                if (!ConsumeEndTime.Equals("0"))
                {
                    strWhere.AppendFormat("  and a.ConsumeTime <= N\'{0}\'", ConsumeEndTime);
                }
                if (!ConsumeLocation.Equals("0"))
                {
                    strWhere.AppendFormat("  and a.ConsumeLocation like N\'%{0}%\'", ConsumeLocation);
                }
                if (!ConsumeIDItem.Equals("0"))
                {
                    strWhere.AppendFormat("  and d.ConsumeItem like N\'%{0}%\'", ConsumeIDItem);
                }
            }
            object[] obj = new object[] { tblName.ToString(), strGetFields.ToString(), strOrder, PageSize, PageIndex, strWhere };
            DataTable dt = new DataTable();
            dt = dalSqlHelper.ExecuteProcedure(obj, ref RsCount);
            if (dt.Rows.Count > 0)
            {
                return dalSqlHelper.DtToSON(dt) + "@" + RsCount.ToString();
            }
            else
            {
                return "";
            }
        }


        /// <summary>
        /// 获取会员消费项目
        /// </summary>
        /// <param name="Member"></param>
        /// <returns></returns>
        public static string GetConsumeItemByMember(string Member)
        {
            sqlobj = new SqlObject();
            StringBuilder sb = new StringBuilder();

            sb.Append(" select a.Id as ConsumeItemId,b.Memo1,b.ConsumeItem from T_CF_OA_MemberConsumeItem a   ")
              .Append(" INNER JOIN T_CF_OA_CardConsumeItem b ON a.ConsumeItemId = b.Id ")
              .Append(" where a.MemberCardId = @MemberCardID ");

            sqlobj.Sql = sb.ToString();
            sqlobj.PramS = new SqlParameter[] { 
               new SqlParameter("@MemberCardID",Member)
            };
            dt = dalSqlHelper.ExecuteSelect(sqlobj);
            return dalSqlHelper.DtToSON(dt);
        }


        /// <summary>
        /// 新增回访时读取基本数据
        /// </summary>
        /// <param name="Member"></param>
        /// <returns></returns>
        public static string GetConsumeInfo(string ConsumeID)
        {
            sqlobj = new SqlObject();
            StringBuilder sb = new StringBuilder();

            sb.Append(" select a.ConsumeID, b.MemberID,c.Name,c.VehicleNo,a.ConsumeTime,a.ConsumeLocation,a.ShopLeader as Leader,e.ConsumeItem    ")
              .Append(" from T_CF_OA_Consume a, T_CF_OA_MemberCard b,T_CF_OA_Member c,T_CF_OA_MemberConsumeItem e  ")
              .Append(" where a.MemberCardID = b.MemberCardID and b.MemberID = c.MemberID and a.ConsumeIDItem = e.Id  and ")
              .Append("  a.ConsumeID = @ConsumeID ");

            sqlobj.Sql = sb.ToString();
            sqlobj.PramS = new SqlParameter[] { 
               new SqlParameter("@ConsumeID",ConsumeID)
            };
            dt = dalSqlHelper.ExecuteSelect(sqlobj);
            return dalSqlHelper.DtToSON(dt);
        }


        /// <summary>
        /// 获取会员消费记录
        /// </summary>
        /// <param name="Member"></param>
        /// <returns></returns>
        public static string GetConsumeHistory(int PageSize, int PageIndex, string Member)
        {
            StringBuilder tblName = new StringBuilder(); //从那些表里面进行查询
            int RsCount = 0;
            tblName.Append("  T_CF_OA_Consume a ,T_CF_OA_MemberConsumeItem b ");

            string strOrder = " a.ConsumeTime desc ";  //排序的字段
            StringBuilder strGetFields = new StringBuilder(); //返回的列datediff(d,updatedate,getdate())
            strGetFields.Append(" a.ConsumeTime,a.ConsumeLocation,b.ConsumeItem,a.ConsumeAmount,a.PaidAmount ");

            StringBuilder strWhere = new StringBuilder(); //查询的条件
            strWhere.Append(" a.IsDel ='0' and a.ConsumeIDItem = b.Id and a.MemberCardID = '" + Member + "' ");

            object[] obj = new object[] { tblName.ToString(), strGetFields.ToString(), strOrder, PageSize, PageIndex, strWhere };
            DataTable dt = new DataTable();
            dt = dalSqlHelper.ExecuteProcedure(obj, ref RsCount);
            if (dt.Rows.Count > 0)
            {
                return dalSqlHelper.DtToSON(dt) + "@" + RsCount.ToString();
            }
            else
            {
                return "";
            }
        }


        /// <summary>
        /// 检查输入的会员密码是否正确
        /// 如果密码正确则返回是否过期值（过期返回1，否则返回0），否则返回Failure
        /// </summary>
        /// <param name="Member"></param>
        /// <returns></returns>
        public static DataTable CheckMemberPWD(string MemberCardID, string MemberPWD)
        {
            sqlobj = new SqlObject();
            StringBuilder sb = new StringBuilder();

            sb.Append(" select a.ExpirationDate   ")
              .Append(" from T_CF_OA_MemberCard a ")
              .Append(" where a.MemberCardID = @MemberCardID and a.Pwd = @pwd  ");

            sqlobj.Sql = sb.ToString();
            sqlobj.PramS = new SqlParameter[] { 
               new SqlParameter("@MemberCardID",MemberCardID),
               new SqlParameter("@pwd",MemberPWD)
            };
            dt = dalSqlHelper.ExecuteSelect(sqlobj);
            return dt;
        }


        /// <summary>
        /// 获取特约商户
        /// </summary>
        /// <returns></returns>
        public static string GetShopAddress(string UserID)
        {
            sqlobj = new SqlObject();
            StringBuilder sb = new StringBuilder();
            if (UserID == "")
            {
                sb.Append("select a.Id,a.ShopName,a.Leader,a.Address from T_CF_OA_Shop a where a.IsDel='0' order by a.Id ");
                sqlobj.PramS = new SqlParameter[] { };
            }
            else
            {
                sb.Append("select b.Id,b.Address from T_CF_User a INNER JOIN T_CF_OA_Shop b ON a.ShopID = b.Id where b.IsDel='0' and a.UserID = @UserID ");
                sqlobj.PramS = new SqlParameter[] { 
                    new SqlParameter("@UserID",UserID)
                };
            }

            sqlobj.Sql = sb.ToString();
            dt = dalSqlHelper.ExecuteSelect(sqlobj);
            return dalSqlHelper.DtToSON(dt);
        }


        /// <summary>
        /// 添加客户实体
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string AddEntity(T_CF_OA_Consume item)
        {
            StringBuilder SB = new StringBuilder();
            sqlobj = new SqlObject();
            SqlObj = new List<SqlObject>();
            SB.Append(" INSERT INTO T_CF_OA_Consume ( ")
               .Append(" MemberCardID,ConsumeTime,ConsumeLocation,ShopName,ShopLeader,ConsumeIDItem,ConsumeAmount,PaidAmount,Memo,Reviser ,ReviseDate ) ");
            SB.Append(" values (@MemberCardID,@ConsumeTime,@ConsumeLocation,@ShopName,@ShopLeader,@ConsumeIDItem,@ConsumeAmount,@PaidAmount,@Memo,@Reviser,@ReviseDate ) ");
            sqlobj.Sql = SB.ToString();

            sqlobj.PramS = new SqlParameter[] { 
               new SqlParameter("@MemberCardID",item.MemberCardID)
               ,new SqlParameter("@ConsumeTime",item.ConsumeTime)
               ,new SqlParameter("@ConsumeLocation",item.ConsumeLocation)
               ,new SqlParameter("@ShopName",item.ShopName)
               ,new SqlParameter("@ShopLeader",item.ShopLeader)
               ,new SqlParameter("@ConsumeIDItem",item.ConsumeIDItem)
               ,new SqlParameter("@ConsumeAmount",item.ConsumeAmount)
               ,new SqlParameter("@PaidAmount",item.PaidAmount)
               ,new SqlParameter("@Memo",item.Memo)
               ,new SqlParameter("@Reviser",item.Reviser)
               ,new SqlParameter("@ReviseDate",item.ReviseDate)
            };
            SqlObj.Add(sqlobj);

            //下面是修改T_CF_OA_MemberCard的Is剩余金额ResidualAmount字段为。
            StringBuilder up_sb = new StringBuilder();
            sqlobj = new SqlObject();
            up_sb.AppendFormat(" update T_CF_OA_MemberCard set ResidualAmount=ResidualAmount-@ConsumeAmount_11 where MemberCardID=@MemberCardID_1 ");
            sqlobj.Sql = up_sb.ToString();
            sqlobj.PramS = new SqlParameter[] { 
                new SqlParameter("@ConsumeAmount_11",item.ConsumeAmount)
                ,new SqlParameter("@MemberCardID_1",item.MemberCardID)
            };
            SqlObj.Add(sqlobj);

            //下面是减少T_CF_OA_MemberConsumeItem表中消费次数。
            StringBuilder up_sb1 = new StringBuilder();
            sqlobj = new SqlObject();
            up_sb1.AppendFormat(" update T_CF_OA_MemberConsumeItem set RemainConsumeCount=RemainConsumeCount-1 where  Id = @ConsumeIDItem_11 ");
            sqlobj.Sql = up_sb1.ToString();
            sqlobj.PramS = new SqlParameter[] { 
                new SqlParameter("@ConsumeIDItem_11",item.ConsumeIDItem)
            };
            SqlObj.Add(sqlobj);

            if (dalSqlHelper.ExecuteTransaction(SqlObj))
            {
                return "Success";
            }
            else
            {
                return "Failure";
            }
        }


        /// <summary>
        /// 删除客户实体
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static string DeleteEntity(string ConsumeID)
        {
            StringBuilder SB = new StringBuilder();
            SqlObj = new List<SqlObject>();
            StringBuilder SB_Product = new StringBuilder();
            SB_Product.Append(" delete from T_CF_OA_Consume where ConsumeID=@ConsumeID    ");

            SqlObject Sql_Obj_Product = new SqlObject();
            Sql_Obj_Product.Sql = SB_Product.ToString();
            Sql_Obj_Product.PramS = new SqlParameter[] { 
               new SqlParameter("@ConsumeID",ConsumeID)
            };
            SqlObj.Add(Sql_Obj_Product);
            if (dalSqlHelper.ExecuteTransaction(SqlObj))
            {
                return "Success";
            }
            else
            {
                return "Failure";
            }
        }


        /// <summary>
        /// 修改实体
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string EditEntity(T_CF_OA_Consume item)
        {
            StringBuilder SB = new StringBuilder();
            SqlObj = new List<SqlObject>();
            SB.Append("  UPDATE T_CF_OA_Consume SET     ")
                  .Append("MemberCardID =@MemberCardID")
                  .Append(",ConsumeTime =@ConsumeTime")
                  .Append(",ConsumeLocation =@ConsumeLocation")
                  .Append(",ShopName =@ShopName")
                  .Append(",ShopLeader =@ShopLeader")
                  .Append(",ConsumeIDItem =@ConsumeIDItem")
                  .Append(",ConsumeAmount =@ConsumeAmount")
                  .Append(",PaidAmount =@PaidAmount")
                  .Append(",Memo =@Memo")
                  .Append(",Reviser =@Reviser")
                  .Append(",ReviseDate =@ReviseDate")
                  .Append("  where ConsumeID=@ConsumeID ");
            SqlObject Sql_Obj = new SqlObject();
            Sql_Obj.Sql = SB.ToString();

            Sql_Obj.PramS = new SqlParameter[] {
                        new SqlParameter("@ConsumeID",item.ConsumeID)
                       ,new SqlParameter("@MemberCardID",item.MemberCardID)
                       ,new SqlParameter("@ConsumeTime",item.ConsumeTime)
                       ,new SqlParameter("@ConsumeLocation",item.ConsumeLocation)
                       ,new SqlParameter("@ShopName",item.ShopName)
                       ,new SqlParameter("@ShopLeader",item.ShopLeader)
                       ,new SqlParameter("@ConsumeIDItem",item.ConsumeIDItem)
                       ,new SqlParameter("@ConsumeAmount",item.ConsumeAmount)
                       ,new SqlParameter("@PaidAmount",item.PaidAmount)
                       ,new SqlParameter("@Memo",item.Memo)
                       ,new SqlParameter("@Reviser",item.Reviser)
                       ,new SqlParameter("@ReviseDate",item.ReviseDate)                   
             };
            SqlObj.Add(Sql_Obj);

            //下面是修改T_CF_OA_MemberCard的Is剩余金额ResidualAmount字段为。
            StringBuilder up_sb = new StringBuilder();
            sqlobj = new SqlObject();
            up_sb.AppendFormat(" update T_CF_OA_MemberCard set ResidualAmount=ResidualAmount-@ConsumeAmount where MemberCardID=@MemberCardID_1 ");
            sqlobj.Sql = up_sb.ToString();
            sqlobj.PramS = new SqlParameter[] { 
                new SqlParameter("@ConsumeAmount",item.ConsumeAmount)
                ,new SqlParameter("@MemberCardID_1",item.MemberCardID)
            };
            SqlObj.Add(sqlobj);

            if (dalSqlHelper.ExecuteTransaction(SqlObj))
            {
                return "Success";
            }
            else
            {
                return "Failure";
            }
        }


        /// <summary>
        /// 修改时查询该条记录
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static string GetOne(string PK)
        {
            StringBuilder tblName = new StringBuilder(); //从那些表里面进行查询
            int RsCount = 0;
            tblName.Append(" T_CF_OA_Consume a,T_CF_OA_MemberCard b,T_CF_OA_Member c,T_CF_User d,T_CF_OA_MemberConsumeItem e  ");

            string strOrder = " a.ConsumeID desc ";  //排序的字段
            StringBuilder strGetFields = new StringBuilder(); //返回的列datediff(d,updatedate,getdate())
            strGetFields.Append(" a.MemberCardID,case b.State when 1 then  '正常'  ")
                        .Append(" when 2 then '挂失' when 3 then '冻结'  when 4 then '未审核' else ''  end State  ")
                        .Append(",b.MemberID,c.Name,b.ResidualAmount ")
                        .Append(",a.consumeTime,a.ConsumeLocation,e.ConsumeItem as ConsumeIDItem,a.ConsumeAmount,a.PaidAmount ,d.UserName,a.ReviseDate,a.Memo ");
            StringBuilder strWhere = new StringBuilder(); //查询的条件
            strWhere.AppendFormat(" a.MemberCardID=b.MemberCardID and b.MemberID=c.MemberID and  a.Reviser=d.UserCode and a.ConsumeIDItem=e.Id and a.ConsumeID=N\'{0}\' ", PK);


            object[] obj = new object[] { tblName.ToString(), strGetFields.ToString(), strOrder, 1, 1, strWhere };
            DataTable dt = new DataTable();
            dt = dalSqlHelper.ExecuteProcedure(obj, ref RsCount);
            if (dt.Rows.Count > 0)
            {
                return dalSqlHelper.DtToSON(dt);
            }
            else
            {
                return "";
            }
        }

    }
}
